from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
db = SQLAlchemy()

# 产品基本信息模型
class Product(db.Model):
    __tablename__ = 'products'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 基本信息
    product_name = db.Column(db.String(100), comment='产品名称')
    product_code = db.Column(db.String(50), comment='产品编号')
    serial_number = db.Column(db.String(50), comment='序列号')
    model = db.Column(db.String(50), comment='型号')
    registration_number = db.Column(db.String(50), comment='注册证号')
    three_certificate_number = db.Column(db.String(50), comment='三证编号')
    purchase_date = db.Column(db.Date, comment='购买日期')
    launch_date = db.Column(db.Date, comment='启用日期')
    warranty_period = db.Column(db.Integer, comment='保修期限(月)')
    product_status = db.Column(db.String(20), comment='产品状态')
    
    # 所属信息
    org_name = db.Column(db.String(100), comment='所属机构')
    org_short_name_c = db.Column(db.String(50), comment='简称')
    department_name = db.Column(db.String(50), comment='所属科室')
    
    # 技术信息
    technical_parameters = db.Column(db.Text, comment='技术参数')
    software_version = db.Column(db.String(50), comment='软件版本')
    hardware_version = db.Column(db.String(50), comment='硬件版本')
    network_address = db.Column(db.String(50), comment='网络地址')
    installation_location = db.Column(db.String(100), comment='安装位置')
    service_life = db.Column(db.Float, comment='使用年限')
    
    # 厂商信息
    manufacturer = db.Column(db.String(100), comment='生产厂家')
    manufacturer_country = db.Column(db.String(50), comment='厂家所在国')
    production_date = db.Column(db.Date, comment='生产日期')
    factory_date = db.Column(db.Date, comment='出厂日期')
    factory_representative = db.Column(db.String(50), comment='厂家代表人')
    factory_contact_info = db.Column(db.String(100), comment='厂家联系方式')
    maintenance_cycle = db.Column(db.Integer, comment='维保周期(月)')
    
    # 财务信息
    price = db.Column(db.Float, comment='价格')
    quantity = db.Column(db.Integer, comment='数量')
    total_amount = db.Column(db.Float, comment='总金额')
    
    # 人员信息
    responsible_person = db.Column(db.String(50), comment='责任人')
    operator = db.Column(db.String(50), comment='操作人')
    
    # 运行状态信息
    running_status = db.Column(db.String(20), comment='运行状态')
    last_maintenance_date = db.Column(db.Date, comment='上次维护日期')
    next_maintenance_date = db.Column(db.Date, comment='下次维护日期')
    maintenance_count = db.Column(db.Integer, comment='维护次数')
    fault_count = db.Column(db.Integer, comment='故障次数')
    completion_rate = db.Column(db.Float, comment='完好率')
    
    # 设备分类信息
    equipment_type = db.Column(db.String(50), comment='设备类型')
    equipment_category = db.Column(db.String(50), comment='设备类别')
    risk_level = db.Column(db.String(20), comment='风险等级')
    
    # 系统信息
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<Product {self.product_name}>'

    def to_dict(self):
        # 运行状态映射
        running_status_map = {
            '0': '正常运行',
            '1': '维修中',
            '2': '停用中',
            '3': '报废',
            '4': '其他'
        }
        comment_map = {
            'id': 'ID',
            'product_code': '产品编号',
            'product_name': '产品名称',
            'serial_number': '序列号',
            'model': '型号',
            'registration_number': '注册证号',
            'three_certificate_number': '三证编号',
            'purchase_date': '购买日期',
            'launch_date': '启用日期',
            'warranty_period': '保修期限(月)',
            'product_status': '产品状态',
            'org_name': '所属机构',
            'org_short_name_c': '机构简称',
            'department_name': '所属科室',
            'technical_parameters': '技术参数',
            'software_version': '软件版本',
            'hardware_version': '硬件版本',
            'network_address': '网络地址',
            'installation_location': '安装位置',
            'service_life': '使用年限',
            'manufacturer': '生产厂家',
            'manufacturer_country': '厂家所在国',
            'production_date': '生产日期',
            'factory_date': '出厂日期',
            'factory_representative': '厂家代表人',
            'factory_contact_info': '厂家联系方式',
            'maintenance_cycle': '维保周期(月)',
            'price': '价格',
            'quantity': '数量',
            'total_amount': '总金额',
            'responsible_person': '责任人',
            'operator': '操作人',
            'running_status': '运行状态',
            'last_maintenance_date': '上次维护日期',
            'next_maintenance_date': '下次维护日期',
            'maintenance_count': '维护次数',
            'fault_count': '故障次数',
            'completion_rate': '完好率',
            'equipment_type': '设备类型',
            'equipment_category': '设备类别',
            'risk_level': '风险等级',
            'is_active': '是否有效',
            'create_time': '创建时间',
            'update_time': '更新时间'
        }
        
        # 返回包含注释的字典（实际导出时使用字段值作为键）
        return {
            comment_map.get(k, k): (running_status_map.get(str(v), v) if k == 'running_status' else v)
            for k, v in {
                'id': self.id,
                'product_code': self.product_code,
                'product_name': self.product_name,
                'serial_number': self.serial_number,
                'model': self.model,
                'registration_number': self.registration_number,
                'three_certificate_number': self.three_certificate_number,
                'purchase_date': self.purchase_date.strftime('%Y-%m-%d') if self.purchase_date else None,
                'launch_date': self.launch_date.strftime('%Y-%m-%d') if self.launch_date else None,
                'warranty_period': self.warranty_period,
                'product_status': self.product_status,
                'org_name': self.org_name,
                'org_short_name_c': self.org_short_name_c,
                'department_name': self.department_name,
                'technical_parameters': self.technical_parameters,
                'software_version': self.software_version,
                'hardware_version': self.hardware_version,
                'network_address': self.network_address,
                'installation_location': self.installation_location,
                'service_life': float(self.service_life) if self.service_life else 0.0,
                'manufacturer': self.manufacturer,
                'manufacturer_country': self.manufacturer_country,
                'production_date': self.production_date.strftime('%Y-%m-%d') if self.production_date else None,
                'factory_date': self.factory_date.strftime('%Y-%m-%d') if self.factory_date else None,
                'factory_representative': self.factory_representative,
                'factory_contact_info': self.factory_contact_info,
                'maintenance_cycle': self.maintenance_cycle,
                'price': float(self.price) if self.price else 0.0,
                'quantity': self.quantity,
                'total_amount': float(self.total_amount) if self.total_amount else 0.0,
                'responsible_person': self.responsible_person,
                'operator': self.operator,
                'running_status': self.running_status,
                'last_maintenance_date': self.last_maintenance_date.strftime('%Y-%m-%d') if self.last_maintenance_date else None,
                'next_maintenance_date': self.next_maintenance_date.strftime('%Y-%m-%d') if self.next_maintenance_date else None,
                'maintenance_count': self.maintenance_count,
                'fault_count': self.fault_count,
                'completion_rate': float(self.completion_rate) if self.completion_rate else 0.0,
                'equipment_type': self.equipment_type,
                'equipment_category': self.equipment_category,
                'risk_level': self.risk_level,
                'is_active': self.is_active,
                'create_time': self.create_time.strftime('%Y-%m-%d %H:%M:%S') if self.create_time else None,
                'update_time': self.update_time.strftime('%Y-%m-%d %H:%M:%S') if self.update_time else None
            }.items()
        }    

# 维修记录模型
class RepairRecord(db.Model):
    __tablename__ = 'repair_records'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='维修ID')
    product_id = db.Column(db.Integer, comment='产品ID')
    product_name = db.Column(db.String(100), comment='产品名称')
    product_code = db.Column(db.String(50), comment='产品编号')
    product_category = db.Column(db.String(50), comment='通用类型')
    product_brand = db.Column(db.String(50), comment='品牌')
    product_model = db.Column(db.String(50), comment='型号')
    org_name = db.Column(db.String(100), comment='所属机构')
    org_shortname_c = db.Column(db.String(20), comment='所属机构简称')
    department_name = db.Column(db.String(50), comment='所属科室')
    
    report_date = db.Column(db.DateTime,nullable=True, comment='报修时间')
    repair_caller = db.Column(db.String(50), comment='报修人')
    repair_answer_date = db.Column(db.DateTime,nullable=True, comment='响应时间')
    repair_type = db.Column(db.String(50), comment='维修类型')
    fault_description = db.Column(db.Text, comment='故障描述')
    repair_result = db.Column(db.String(50), comment='维修结果')
    repair_content = db.Column(db.Text, comment='维修内容')
    repair_cost = db.Column(db.Float, comment='维修费用')
    repair_duration = db.Column(db.Float, comment='维修时长')
    repair_answer_duration = db.Column(db.Float, comment='响应时长')
    repair_person = db.Column(db.String(50), comment='维修人员')
    repair_company = db.Column(db.String(100), comment='维修公司')
    repair_status = db.Column(db.String(20), comment='维修状态')
    expected_completion_date = db.Column(db.Date, comment='预计完成日期')
    actual_completion_date = db.Column(db.DateTime,nullable=True, comment='实际完成日期')
    parts_replaced = db.Column(db.Text, comment='更换配件')
    remarks = db.Column(db.Text, comment='备注')
    proof = db.Column(db.String(100), comment='工单路径')
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now,nullable=True, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now,nullable=True, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<RepairRecord {self.id}>'

# 组织机构模型
class Organization(db.Model):
    __tablename__ = 'organizations'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    org_name = db.Column(db.String(100), comment='机构名称')
    org_code = db.Column(db.String(50), comment='机构代码')
    org_level = db.Column(db.String(20), comment='机构等级')
    org_type = db.Column(db.String(20), comment='机构类型')
    parent_id = db.Column(db.Integer, comment='上级机构ID')
    address = db.Column(db.String(200), comment='地址')
    contact_person = db.Column(db.String(50), comment='联系人')
    contact_phone = db.Column(db.String(20), comment='联系电话')
    short_name = db.Column(db.String(50), comment='前缀')
    short_name_c = db.Column(db.String(50), comment='简称')
    location_x = db.Column(db.Float, comment='地理位置X坐标')
    location_y = db.Column(db.Float, comment='地理位置Y坐标')
    
    # 统计信息
    equipment_count = db.Column(db.Integer, comment='设备数量')
    total_asset_value = db.Column(db.Float, comment='资产总值')
    maintenance_rate = db.Column(db.Float, comment='维修率')
    equipment_age_index = db.Column(db.Float, comment='设备老化指数')
    fault_count = db.Column(db.Integer, comment='故障次数')
    completion_rate = db.Column(db.Float, comment='完好率')
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<Organization {self.org_name}>'

# 部门模型
class Department(db.Model):
    __tablename__ = 'departments'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    dept_name = db.Column(db.String(50), comment='部门名称')
    org_id = db.Column(db.Integer, comment='所属机构ID')
    org_name = db.Column(db.String(100), comment='所属机构名称')
    dept_code = db.Column(db.String(50), comment='部门代码')
    dept_head = db.Column(db.String(50), comment='部门负责人')
    contact_phone = db.Column(db.String(20), comment='联系电话')
    equipment_count = db.Column(db.Integer, comment='设备数量')
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<Department {self.dept_name}>'

# 设备类型模型
class EquipmentType(db.Model):
    __tablename__ = 'equipment_types'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    type_name = db.Column(db.String(50), comment='类型名称')
    type_code = db.Column(db.String(20), comment='类型代码')
    description = db.Column(db.Text, comment='描述')
    equipment_count = db.Column(db.Integer, comment='设备数量')
    total_value = db.Column(db.Float, comment='总价值')
    age_index = db.Column(db.Float, comment='老化指数')
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<EquipmentType {self.type_name}>'

# 运行状态记录模型
class OperationRecord(db.Model):
    __tablename__ = 'operation_records'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    product_id = db.Column(db.Integer, comment='产品ID')
    product_name = db.Column(db.String(100), comment='产品名称')
    product_code = db.Column(db.String(50), comment='产品编号')
    record_date = db.Column(db.Date, comment='记录日期')
    operation_status = db.Column(db.String(20), comment='运行状态')
    operation_hours = db.Column(db.Float, comment='运行时长')
    operator = db.Column(db.String(50), comment='操作人')
    remarks = db.Column(db.Text, comment='备注')
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<OperationRecord {self.id}>'

# 用户模型
from werkzeug.security import generate_password_hash, check_password_hash

class User(db.Model):
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), comment='用户名')
    password = db.Column(db.String(100), comment='密码')
    real_name = db.Column(db.String(50), comment='真实姓名')
    org_code = db.Column(db.String(20), comment='所属机构code')
    org_name = db.Column(db.String(100), comment='所属机构名称')
    dept_id = db.Column(db.Integer, comment='所属部门ID')
    dept_name = db.Column(db.String(50), comment='所属部门名称')
    
    role = db.Column(db.String(20), default='user', comment='角色')
    phone = db.Column(db.String(20), comment='电话')
    email = db.Column(db.String(50), comment='邮箱')
    last_login = db.Column(db.DateTime, comment='最后登录时间')
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def is_admin(self):
        return self.role == 'admin'
    
    def set_password(self, password):
        self.password = generate_password_hash(password)
    def check_password(self, password):
        return check_password_hash(self.password, password)
        
    def __repr__(self):
        return f'<User {self.username}>'

# 统计数据模型
class StatisticsData(db.Model):
    __tablename__ = 'statistics_data'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    org_id = db.Column(db.Integer, comment='机构ID')
    org_name = db.Column(db.String(100), comment='机构名称')
    org_shortname_c = db.Column(db.String(100), comment='机构简称')
    stat_date = db.Column(db.Date, comment='统计日期')
    equipment_count = db.Column(db.Integer, comment='设备数量')
    total_value = db.Column(db.Float, comment='总价值')
    fault_count = db.Column(db.Integer, comment='故障次数')
    repair_complete_count = db.Column(db.Integer, comment='维修完成次数')
    repair_complete_rate = db.Column(db.Float, comment='维修完成率')
    health_rate = db.Column(db.Float, comment='设备完好率')
    age_index = db.Column(db.Float, comment='设备年限指数')
    maintain_plan_count = db.Column(db.Integer, comment='保养计划次数')
    maintain_complete_count = db.Column(db.Integer, comment='保养完成次数')
    maintain_complete_rate = db.Column(db.Float, comment='保养完成率')
    inspection_plan_count = db.Column(db.Integer, comment='巡检计划次数')
    inspection_complete_count = db.Column(db.Integer, comment='巡检完成次数')
    inspection_complete_rate = db.Column(db.Float, comment='巡检完成率')
    
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<StatisticsData {self.id}>'

# 保养记录模型
class MaintainRecord(db.Model):
    __tablename__ = 'maintain_records'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    product_id = db.Column(db.Integer, comment='产品ID')
    product_name = db.Column(db.String(100), comment='产品名称')
    product_code = db.Column(db.String(50), comment='产品编号')
    org_id = db.Column(db.Integer, comment='机构ID')
    org_name = db.Column(db.String(100), comment='机构名称')
    dept_id = db.Column(db.Integer, comment='部门ID')
    dept_name = db.Column(db.String(50), comment='部门名称')
    scheduled_date = db.Column(db.Date, comment='计划保养日期')
    actual_date = db.Column(db.Date, comment='实际保养日期')
    maintenance_type = db.Column(db.String(50), comment='保养类型')
    maintenance_content = db.Column(db.Text, comment='保养内容')
    maintenance_result = db.Column(db.String(50), comment='保养结果')
    maintenance_person = db.Column(db.String(50), comment='保养人员')
    maintenance_company = db.Column(db.String(100), comment='保养公司')
    completion_status = db.Column(db.String(20), comment='完成状态')
    remarks = db.Column(db.Text, comment='备注')
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<MaintainRecord {self.id}>'

# 设备运行指标模型
class EquipmentMetrics(db.Model):
    __tablename__ = 'equipment_metrics'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    product_id = db.Column(db.Integer, comment='产品ID')
    record_date = db.Column(db.Date, comment='记录日期')
    
    # 根据雷达图指标
    reliability = db.Column(db.Float, comment='可靠性')
    stability = db.Column(db.Float, comment='稳定性')
    efficiency = db.Column(db.Float, comment='效率')
    safety = db.Column(db.Float, comment='安全性')
    economy = db.Column(db.Float, comment='经济性')
    
    # 其他指标
    running_time = db.Column(db.Float, comment='运行时间(小时)')
    downtime = db.Column(db.Float, comment='停机时间(小时)')
    maintenance_cost = db.Column(db.Float, comment='维护成本')
    energy_consumption = db.Column(db.Float, comment='能耗')
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<EquipmentMetrics {self.id}>'

# 财务收支记录模型
class FinancialRecord(db.Model):
    __tablename__ = 'financial_records'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    org_id = db.Column(db.Integer, comment='机构ID')
    org_name = db.Column(db.String(100), comment='机构名称')
    record_date = db.Column(db.Date, comment='记录日期')
    
    # 根据截图中的财务数据
    business_income = db.Column(db.Float, comment='业务收入')
    medical_income = db.Column(db.Float, comment='医疗收入')
    equipment_expenses = db.Column(db.Float, comment='设备支出')
    maintenance_expenses = db.Column(db.Float, comment='维保支出')
    income_ratio = db.Column(db.Float, comment='收入比例')
    expense_ratio = db.Column(db.Float, comment='支出比例')
    
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')
    
    def __repr__(self):
        return f'<FinancialRecord {self.id}>'

class InspectionRecord(db.Model):
    __tablename__ = 'inspection_records'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    product_id = db.Column(db.Integer, comment='设备ID')
    product_name = db.Column(db.String(100), comment='设备名称')
    product_code = db.Column(db.String(50), comment='设备编号')
    org_id = db.Column(db.Integer, comment='机构ID')
    org_name = db.Column(db.String(100), comment='机构名称')
    dept_id = db.Column(db.Integer, comment='部门ID')
    dept_name = db.Column(db.String(50), comment='所属科室')
    scheduled_date = db.Column(db.Date, comment='巡检计划日期')
    actual_date = db.Column(db.Date, comment='巡检完成日期')
    inspection_person = db.Column(db.String(50), comment='巡检人')
    inspection_company = db.Column(db.String(50), comment='巡检公司')
    inspection_type = db.Column(db.String(50), comment='巡检类型')
    inspection_content = db.Column(db.Text, comment='巡检内容')
    inspection_result = db.Column(db.String(50), comment='巡检结果')
    completion_status = db.Column(db.String(20), comment='完成状态')
       
    issues_found = db.Column(db.Text, comment='发现问题')
    action_taken = db.Column(db.Text, comment='处理措施')
    remarks = db.Column(db.Text, comment='备注')
    is_active = db.Column(db.Boolean, default=True, comment='是否有效')
    create_time = db.Column(db.DateTime, default=datetime.now, comment='创建时间')
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment='更新时间')

    def __repr__(self):
        return f'<InspectionRecord {self.id}>'
